package com.smart.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import com.smart.model.NoticeTemplate;
import com.smart.util.DBUtil;

public class NoticeTemplateDAO {

	public void saveTemplate(NoticeTemplate template) throws SQLException {
		Connection conn = DBUtil.getConnection();
		String sql="insert into notice_template(community_id, name, title, content) values(?,?,?,?);";
		PreparedStatement pSt = conn.prepareCall(sql);
		pSt.setInt(1, template.getCommunityId());
		System.out.println("name:"+template.getName());
		pSt.setString(2, template.getName());
		pSt.setString(3, template.getTitle());
		pSt.setString(4, template.getContent());
		pSt.execute();
	} 
	
	public NoticeTemplate getTemplateById(int id) throws SQLException {
		Connection conn = DBUtil.getConnection();
		String sql="select * from notice_template where id=?;";
		PreparedStatement pSt = conn.prepareCall(sql);
		pSt.setInt(1, id);
		ResultSet res = pSt.executeQuery();
		NoticeTemplate template = new NoticeTemplate();
		if(res.next()){
			template.setId(res.getInt("id"));
			template.setName(res.getString("name"));
			template.setCommunityId(res.getInt("community_id"));
			template.setTitle(res.getString("title"));
			template.setContent(res.getString("content"));
			SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
			String createTime=sdf.format(res.getTimestamp("create_time"));
			template.setCreateTime(createTime);
		}
		return template;
	}
	public List<NoticeTemplate> getTemplateList() throws SQLException {
		Connection conn = DBUtil.getConnection();
		String sql = "select id, name from notice_template;";
		Statement st = conn.createStatement();
		ResultSet res = st.executeQuery(sql);
		List<NoticeTemplate> list = new ArrayList<NoticeTemplate>();
		while(res.next()) {
			NoticeTemplate template = new NoticeTemplate();
			template.setName(res.getString("name"));
			template.setId(res.getInt("id"));
			list.add(template);
		}
		return list;
	}
}
